﻿using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using nhCore.db;
using nhCore.nMessage;
using System.Data;
using System.Data.Common;

namespace nhCore.nhLib.sql;

public class SQLite
{
    /// <summary>
    /// 检测字段，如不存在则增加
    /// </summary>
    /// <param name="table">表</param>
    /// <param name="column">字段</param>
    /// <param name="type">字段类型</param>
    /// <param name="define">字段定义</param>
    /// <returns>sql语句</returns>
    public static bool CheckAndAddColumn(DbContext db, string table, string column, string type, string define = "")
    {
        bool ret = false;
        DbConnection dbConnection = db.Database.GetDbConnection();
        var queryCmd = dbConnection.CreateCommand();
        queryCmd.CommandText = $"SELECT 1 FROM sqlite_master WHERE name = '{table}' AND sql LIKE '%{column}%'";

        try
        {
            dbConnection.Open();
            DbDataReader reader = queryCmd.ExecuteReader();
            if (!reader.HasRows)
            {
                var addCmd = dbConnection.CreateCommand();
                addCmd.CommandText = $"ALTER TABLE {table} ADD COLUMN {column} {type} {define}";
                if (addCmd.ExecuteNonQuery() > 0)
                {
                    ret = true;
                }
            }
        }
        catch (Exception ex)
        {
            NMessage.Error($"{table}表建立【{column}】失败：{ex.Message}");
        }
        finally
        {
            dbConnection.Close();
        }
        return ret;
    }

    public static bool InsertData(DbContext db, string table, List<string> columns, List<string> values)
    {
        bool ret = false;
        values = values.Select(s => $"'{s}'").ToList();
        DbConnection dbConnection = db.Database.GetDbConnection();
        var insertCmd = dbConnection.CreateCommand();
        insertCmd.CommandText = $"INSERT INTO {table} ({string.Join(",", columns)}) VALUES ({string.Join(",", values)})"; ;

        try
        {
            dbConnection.Open();
            if (insertCmd.ExecuteNonQuery() > 0)
            {
                ret = true;
            }
        }
        catch (Exception ex)
        {
            NMessage.Error($"{table}表插入数据{insertCmd.CommandText}失败：{ex.Message}");
        }
        finally
        {
            dbConnection.Close();
        }

        return ret;
    }

    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="sensor"></param>
    /// <param name="begin"></param>
    /// <param name="end"></param>
    /// <param name="hourOffset"></param>
    /// <returns>字典列表为记录，List为表格</returns>
    public static ReturnData? QueryDatas(Station sensor, DateTime begin, DateTime end, int hourOffset)
    {
        begin = begin.AddHours(hourOffset);
        end = end.AddHours(hourOffset);
        string qTime = $"STRFTIME('{con_sqlite_time_format}', {nameof(Data.Time)}) ";;
        (var columns, _, var columnIndexs) = GroupColumn([.. sensor.ElementConfigs]);
        string sql = @$"SELECT Time {columns} 
            FROM {nameof(EnvDbContext.Datas)}
            WHERE {nameof(Data.Id)} = {sensor.Id} AND {qTime}>= '{DateTimeFormat(begin)}' AND {qTime} <= '{DateTimeFormat(end)}' 
            ORDER BY `{nameof(Data.Time)}` DESC";
        ReturnData returnData = null!;

        using var dbConnection = new SqliteConnection(EnvDbContext.con_str_conn_str);


        //using EnvDbContext db = new();
        //DbConnection dbConnection = db.Database.GetDbConnection();
        var cmd = dbConnection.CreateCommand();
        cmd.CommandText = sql;

        try
        {
            dbConnection.Open();
            DbDataReader reader = cmd.ExecuteReader();
            returnData = new ReturnData(reader, columnIndexs);
        }
        catch (Exception ex)
        {
            NMessage.Error($"读{nameof(EnvDbContext.Datas)}表失败：{ex.Message}");
        }
        finally
        {
            dbConnection.Close();
        }

        return returnData;
    }

    /// <summary>
    /// SQLite的时间偏移语句
    /// </summary>
    /// <param name="hourOffset"></param>
    /// <returns>SQL</returns>
    private static string DateAdd(int hourOffset)
    {
        return $"DATETIME({nameof(Data.Time)},'-{hourOffset} hour')";
    }

    private static string DateTimeFormat(DateTime dateTime)
    {
        return $"{dateTime:yyyy-MM-dd HH:mm:ss}";
    }

    private const string con_sqlite_time_format = "%Y-%m-%d %H:$M:$S";

    /// <summary>
    /// 日报，按小时查询
    /// </summary>
    /// <param name="sensor"></param>
    /// <param name="begin"></param>
    /// <param name="end"></param>
    /// <param name="hourOffset"></param>
    /// <returns></returns>
    public static ReturnData? QueryReportDay(Station sensor, DateTime begin, DateTime end, int hourOffset, string sqlTime = "%Y-%m-%d %H:00")
    {
        string sqlHourTime = $"STRFTIME('{sqlTime}', {nameof(Data.Time)}) ";
        string qTime = $"STRFTIME('{con_sqlite_time_format}', {DateAdd(hourOffset)}) ";
        string sql = $"SELECT {sqlHourTime} AS _Time ";

        (var columns, _, var columnIndexs) = GroupColumn([.. sensor.ElementConfigs], true);
        sql += columns;

        sql += @$" FROM {nameof(EnvDbContext.Datas)} 
            WHERE {nameof(Data.Id)} = {sensor.Id} AND {qTime} >= '{DateTimeFormat(begin)}' AND {qTime} <= '{DateTimeFormat(end)}' 
            GROUP BY {sqlHourTime} 
            ORDER BY {sqlHourTime} DESC";
        ReturnData returnData = null!;

        using EnvDbContext db = new();
        DbConnection dbConnection = db.Database.GetDbConnection();
        var cmd = dbConnection.CreateCommand();
        cmd.CommandText = sql;

        try
        {
            dbConnection.Open();
            DbDataReader reader = cmd.ExecuteReader();
            returnData = new ReturnData(reader, columnIndexs);
        }
        catch (Exception ex)
        {
            NMessage.Error($"读{nameof(EnvDbContext.Datas)}表失败：{ex.Message}");
        }
        finally
        {
            dbConnection.Close();
        }

        return returnData;
    }

    /// <summary>
    /// 月报，按天查询
    /// </summary>
    /// <param name="sensor"></param>
    /// <param name="begin"></param>
    /// <param name="end"></param>
    /// <param name="hourOffset"></param>
    /// <returns></returns>
    public static ReturnData? QueryReportMonth(Station sensor, DateTime begin, DateTime end, int hourOffset)
    {        
        return QueryReportDay(sensor, begin, end, hourOffset, "%Y-%m-%d");
    }

    public static ReturnData? QueryReportYear(Station sensor, DateTime begin, DateTime end, int hourOffset)
    {
        return QueryReportDay(sensor, begin, end, hourOffset, "%Y-%m"); ;
    }

    /// <summary>
    /// 根据配置组列
    /// </summary>
    /// <param name="configs"></param>
    /// <param name="statistics">是否是统计</param> 
    /// <param name="hasHourOffset">是否有时间偏移</param>
    /// <returns>SQL SELECT 后的列, From后的列，列对应的寄存器地址</returns>
    private static (string, string, List<int>) GroupColumn(List<ElementConfig> configs, bool statistics = false, bool hasHourOffset = false)
    {
        string selectColumns = string.Empty;
        string formColumns = string.Empty;
        List<int> columnIndex = [];
        int i = 0;
        foreach (ElementConfig cfg in configs)
        {
            short regAddr = cfg.StationReg;
            if (statistics)
            {
                if (!cfg.Statistics.Equals(ElementConfig.con_NOT, StringComparison.CurrentCultureIgnoreCase))
                {
                    selectColumns += string.Format(",{0}(A{1}) AS _A{1}", cfg.Statistics, regAddr);
                    if (hasHourOffset)
                    {
                        formColumns += $",{cfg.GetDataFieldName()}";
                    }
                    columnIndex.Add(i);
                }
            }
            else
            {
                selectColumns += $",{cfg.GetDataFieldName()}";
                columnIndex.Add(i);
            }
            i++;
        }
        return (selectColumns, formColumns, columnIndex);
    }
}
